package com.aischool.service;

import java.util.ArrayList;
import java.util.List;

import com.aischool.params.AiStudentParams;
import com.aischool.utils.RegexUtil;
import com.aischool.utils.StateEnum;
import com.aischool.utils.StringUtil;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

public class AiStudentService {

	/**
	 * 获取分页数据
	 * @param params
	 * @return
	 */
	public Page<Record> getPage(AiStudentParams params){
		String sql_select = "SELECT a.*, c.class_name ";
		String sql_from   =	"FROM ai_student a " + 
							"INNER JOIN ai_class c ON c.class_id = a.class_id ";
		String sql_where  =	"WHERE a.student_stateid >= 0 ";
		String sql_order = "ORDER BY a.student_id DESC ";
		
		//System.err.println(JSONObject.toJSONString(params));
		
		List<Object> listObj = new ArrayList<Object>();
		if (params.getState() != StateEnum.ALL.getState()) {
			sql_where =	"WHERE a.student_stateid = ?";
			listObj.add(params.getState());
		}
		
		if (params.getOrgId()>0) {
			sql_where += " AND a.org_id = ? ";
			listObj.add(params.getOrgId());
		}
		
		if (StrKit.notBlank(params.getKeywords())) {
			sql_where += " AND a.student_name LIKE ? ";
			listObj.add("%" + params.getKeywords() + "%");
		}
		return Db.paginate(params.getPageNo(), params.getPageSize(), sql_select, sql_from + sql_where + sql_order, listObj.toArray());
	}
	
	public List<Record> getStudents(int class_id){
		return Db.find("SELECT a.* FROM ai_student a WHERE a.student_stateid=1 AND a.class_id = ?", class_id);
	}
	
	public Record getOne(int student_id) {
		return Db.findFirst("SELECT a.*, c.class_name "
				+ "FROM ai_student a "
				+ "INNER JOIN ai_class c ON c.class_id = a.class_id "
				+ "WHERE a.student_id = ?", student_id);
	}
	
	public Record getOne(int class_id, String student_name, int student_sno) {
		return Db.findFirst("SELECT a.* FROM ai_student a "
				+ "WHERE a.class_id = ? AND a.student_name = ? AND a.student_sno = ?", 
				class_id, student_name, student_sno);
	}
	
	public boolean del(int student_id) {
		int ir = Db.delete("DELETE FROM ai_student WHERE student_id = ?", student_id);
		return ir > 0;
	}
	
	public boolean delForOrg(int org_id, int student_id) {
		int ir = Db.delete("DELETE FROM ai_student WHERE org_id = ? AND student_id = ?", org_id, student_id);
		return ir > 0;
	}
	
	public boolean delMore(String ids) {
		if (StrKit.isBlank(ids)) {
			return false;
		}
		if (RegexUtil.isSqlIds(ids)) {
			int ir = Db.delete("DELETE FROM ai_student a " + 
					"USING (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS tt " +
					"WHERE a.student_id = CAST(tt.ids AS INTEGER)", 
					StringUtil.trim(ids, ','));
			return ir > 0;
		}
		
		return false;
	}
	
	public boolean delMoreForOrg(int org_id, String ids) {
		if (StrKit.isBlank(ids)) {
			return false;
		}
		if (RegexUtil.isSqlIds(ids)) {
			int ir = Db.delete("DELETE FROM ai_student a " + 
					"USING (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS tt " +
					"WHERE a.org_id = ? AND a.student_id = CAST(tt.ids AS INTEGER)", 
					StringUtil.trim(ids, ','), org_id);
			return ir > 0;
		}
		
		return false;
	}
}
